Sub queries
The Basics A sub query is exactly what it sounds like, a query within a query. Of course, this is not always the case because it could be a query within a query within a query, or even a query within a query within a query within a query. Thats the beauty of it, a sub query can reside within a sub query, that itself can reside inside another sub query. To infinity and beyond. A sub query is as the name implies. It is just a query that lives inside a query. By query of course we mean a SELECT statement. Sub queries can generally be broken into three broad categories 1. Single value or row 2. Multiple rows 3. Multiple columns The names are fairly accurate descriptors. Single value or single row is used to send a single value or row, whereas a multiple row or multiple column sends a list of rows or columns. Each of these three categories has a distinct use in SQL, but it is beyond the scope of this article. Simplifying Work One of the biggest benefits to sub queries is that they allow a complex problem to be broken up into multiple less complex parts. Instead of trying to do everything in one massive select statement the author can instead break each part of the select statement into a sub query. This not only eases the conceptual design of a statement, but lessens the work any one computer has to do. Instead of sending whole tables, the database only sends a series of results. When tables reach row lists of thousands this becomes a very big deal. Example: Select fligth_no, orig, dest, fare From flight Where Orig = ‘PHX’ and Dest = ‘LAX’ and fare < (Select avg(fare) from flight Where Orig = ‘PHX’ and Dest = ‘LAX’) Saves Network Bandwidth With a single SQL statement all the work is done by one machine, and every machine sends whole tables across the network at a time. The tables are received and the work is done on them. With sub queries the work is done before the table information is sent, so instead of receiving a table with thousands of rows, then having to narrow down those rows into something usable, one can narrow down the table to something usable and then send only those rows and columns selected. Of course these sub queries can just as easily go to another sub query. The deeper the nesting (the more sub queries the sub query resides in) the more efficient this becomes. Creating Lists Another huge advantage to a sub query is that it can send a list that another query or object can use. One great example is in a constraint, where a constraint must be in a list of objects. If this list of objects is dynamic it becomes impossible to do this with just a query, however with a sub query it becomes a walk in the park. Example Add Constraint a_constraint Check (english in('big', 'red', 'light', 'heavy', 'thin')) Add Constraint a_constraint Check (english in(select adverbs from list))